# -*- coding: utf-8 -*-
# created by Src - 一路好时光

#pip install openpyxl
#打开excel
#增删改查
#花式遍历
#修改单元格样式


import datetime
#import openpyxl
from openpyxl import Workbook,load_workbook
wb = Workbook()#实例化
#获取当前active的Sheet
print(wb.active)#<Worksheet "Sheet">
sheet = wb.active
print(sheet.title)##Sheet

sheet.title = "src的Sheet"#Sheet的名字
# ⽅式⼀：数据可以直接分配到单元格中(可以输⼊公式)
sheet["C9"] = "test"
sheet["B9"] = "src"
# ⽅式⼆：可以附加⾏，从第⼀列开始附加(从最下⽅空⽩处，最左开始)(可以输⼊多⾏)
sheet.append(['11','22','33'])
# ⽅式三：Python 类型会被⾃动转换
sheet['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
wb.save("src的Sheet.xlsx")



wb = load_workbook("python_study.xlsx")
print(wb.sheetnames)#['python']
#DeprecationWarning: Call to deprecated function get_sheet_by_name (Use wb[sheetname]).
#print(wb.get_sheet_by_name("python"))#<Worksheet "python">
print(wb["python"])#<Worksheet "python">
sheet = wb["python"]
print(sheet["A1"])#<Cell 'python'.A1>
print(sheet["A1"].value)#姓名

#A1-A4的范围
print(sheet["A1:A4"])#((<Cell 'python'.A1>,), (<Cell 'python'.A2>,), (<Cell 'python'.A3>,), (<Cell 'python'.A4>,))

for item in sheet["A1:A4"]:
    print(item[0].value)

# 姓名
# 111
# 112
# 113

for row in sheet:
    print(row)
    for cell in row:
        print(cell.value)

# (<Cell 'python'.A1>, <Cell 'python'.B1>, <Cell 'python'.C1>, <Cell 'python'.D1>)
# 姓名
# 性别
# 年龄
# 省份证号
# (<Cell 'python'.A2>, <Cell 'python'.B2>, <Cell 'python'.C2>, <Cell 'python'.D2>)
# 111
# 1
# 23
# aaa


for row in sheet:
    # print(row)
    for cell in row:
        print(cell.value,end="\t")
    print()
# 姓名	性别	年龄	省份证号
# 111	1	23	aaa
# 112	2	24	bbb
# 113	3	25	ccc
# 114	4	26	ddd
# 115	5	27	eee
# 116	6	28	fff

for row in sheet.iter_rows(min_row=2,max_row=3,max_col=3):
    # print(row)
    for cell in row:
        print(cell.value,end="\t")
    print()
# 111	1	23
# 112	2	24

#循环列
for column in sheet.columns:
    # print(row)
    for cell in column:
        print(cell.value,end="\t")
    print()
# 姓名	111	112	113	114	115	116
# 性别	1	2	3	4	5	6
# 年龄	23	24	25	26	27	28
# 省份证号	aaa	bbb	ccc	ddd	eee	fff

#指定列
for col in sheet.iter_cols(min_col=2,max_col=3,max_row=3):
    # print(row)
    for cell in col:
        print(cell.value,end="\t")
    print()
# 性别	1	2
# 年龄	23	24